package com.java.database;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.sql.*;
import java.util.Arrays;


/**
 * @author wangning
 * @create 2022-04-27 14:29
 */
public class JDBCTest {

	@Test
	public void test01() throws SQLException {
		//1.注册驱动
		DriverManager.registerDriver(new Driver());
		//2.获取数据库连接
		String url = "jdbc:mysql://localhost:3306/db10?useUnicode=true&characterEncoding=utf-8";
		Connection conn = DriverManager.getConnection(url,"root","root");
		//3.获取Statement对象
		Statement statement = conn.createStatement();
		//4.执行SQL
		String sql = "select * from emp";
		ResultSet rs = statement.executeQuery(sql);
		//5.处理结果
		ResultSetMetaData metaData = rs.getMetaData();
		//		System.out.println(metaData.getColumnName(1));
		while (rs.next()) {


			//获取第一列的值（两种方法都可以）
//			System.out.println(rs.getInt(1));
			System.out.println(rs.getInt("id"));
		}
		//6.释放资源
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				rs.close();
			}
		}
//		rs.close();
		statement.close();
		conn.close();
	}


	/**
	 * 实现批处理，向表中插入100条数据
	 */
	@Test
	public void test02() throws SQLException {
		//1.注册驱动
		DriverManager.registerDriver(new Driver());
		//2.获取数据库连接
		String url = "jdbc:mysql://localhost:3306/db10?useUnicode=true&characterEncoding=utf-8";
		Connection conn = DriverManager.getConnection(url, "root", "root");
		//3.获取传输器
		Statement statement = conn.createStatement();
		//4.执行sql
		for (int i = 0; i < 100; i++) {
			String sql = "INSERT INTO `db10`.`emp`(`id`, `name`, `gender`, `birthday`, `dept`, `job`, `sal`, `bonus`) VALUES (null, '王海涛', '男', '1995-03-25', '培优部', '金牌讲师', 1800, 300);";
			//把SQL打成一个批次
			statement.addBatch(sql);

		}
		//统一发送给服务器
		int[] ints = statement.executeBatch();
		System.out.println("ints = " + Arrays.toString(ints));
		statement.close();
		conn.close();

	}

	/**
	 * 实现批处理，向表中插入100条数据
	 */
	@Test
	public void test03() throws SQLException {
		//1.注册驱动
		DriverManager.registerDriver(new Driver());
		//2.获取数据库连接
		String url = "jdbc:mysql://localhost:3306/db10?useUnicode=true&characterEncoding=utf-8";
		Connection conn = DriverManager.getConnection(url, "root", "root");
		//3.获取传输器
		String sql = "INSERT INTO `db10`.`emp`(`id`, `name`, `gender`, `birthday`, `dept`, `job`, `sal`, `bonus`) VALUES (null, ?, '男', '1995-03-25', '培优部', '金牌讲师', 1800, 300);";
		PreparedStatement ps = conn.prepareStatement(sql);
		//设置参数
		for (int i = 0; i < 100; i++) {

			//给第几个问号赋值
			ps.setString(1, "ps" + i);
			ps.addBatch();
		}
		int[] ints = ps.executeBatch();
		System.out.println(ints.length);
		ps.close();

		conn.close();

	}


	/**
	 * 实现批处理，向表中插入100条数据
	 * 优化事务，不需要每条SQL都进行事务管理
	 * jdbc默认是开启事务管理的。
	 * 100条sql就需要开启关闭100次，这是非常消耗资源的，在批处理的时候可以优化
	 * 3.1关闭jdbc自动管理事务的功能
	 * 3.2手动提交事务
	 */
	@Test
	public void test04() throws SQLException {
		//1.注册驱动
		DriverManager.registerDriver(new Driver());
		//2.获取数据库连接
		String url = "jdbc:mysql://localhost:3306/db10?useUnicode=true&characterEncoding=utf-8";
		Connection conn = DriverManager.getConnection(url, "root", "root");

		//关闭自动事务管理
		conn.setAutoCommit(false);
		//3.获取传输器
		Statement statement = conn.createStatement();
		//4.执行sql
		for (int i = 0; i < 100; i++) {
			String sql = "INSERT INTO `db10`.`emp`(`id`, `name`, `gender`, `birthday`, `dept`, `job`, `sal`, `bonus`) VALUES (null, '王海涛', '男', '1995-03-25', '培优部', '金牌讲师', 1800, 300);";
			//把SQL打成一个批次
			statement.addBatch(sql);

		}
		//统一发送给服务器
		int[] ints = statement.executeBatch();
		//需要在此处手动提交事务
		conn.commit();
		System.out.println("ints = " + Arrays.toString(ints));
		statement.close();
		conn.close();

	}

}

